﻿using MySql.Data.MySqlClient;
using newBlog.CenterInc.model;
using newBlog.inc.SqlINC;
using System.Collections.Generic;

namespace newBlog.CenterInc.Ctrltor
{
    public class User_inc
    {
        //定义与用户表交互的全局函数
        sqlConnect Conn = new sqlConnect();
        function function = new function();
        //检测数据库是否有对于数据
        public int SelectedUser(string username, string password)
        {
            string sql = "select count(Blog_user_id) from blog_user where Blog_user_name='{0}' and Blog_user_Password='{1}' and Blog_user_state='1'";
            int count = Conn.DataValue(string.Format(sql, username, password));
            if (count > 0)
            {
                return count;
            }
            return 0;//表示无数据
        }//判断指定用户名，密码是否有内容

        public string SelectEmail(string username, string password)
        {
            string sql = "select Blog_user_email from blog_user where Blog_user_name='{0}' and Blog_user_Password='{1}'";
            string value = Conn.DataOne(string.Format(sql, username, password));
            if (value == null)
            {
                return null;
            }
            return value;
        }//通过id和用户名获取用户的邮箱

        //获取数据库的用户内容，并保存到loginDao
        public LoginDao UserLoginMsg(string username, string password)
        {
            string sql = "select Blog_user_id,Blog_user_name,Blog_user_Password,Blog_user_Grade,Blog_user_email from blog_user where Blog_user_name='{0}' and Blog_user_Password='{1}' ";
            object[] arr = Conn.DataRowOne(string.Format(sql, username, password));

            return new LoginDao((int)arr[0], (string)arr[1], (string)arr[2], (int)arr[3], (string)arr[4]);
        }

        public bool SelectEmailUser(string email)
        {
            string sql = "select count(Blog_user_id) from blog_user where Blog_user_email='{0}'";
            if (Conn.DataValue(string.Format(sql, email)) > 0)
            {
                return true;
            }
            return false;
        }//根据邮箱查找用户
        public bool UserRegister(registerDao registerDao)
        {
            string sql = "insert into blog_user(Blog_user_name,Blog_user_Password,Blog_user_email) values('{0}','{1}','{2}')";
            if (Conn.AddDelUp(string.Format(sql, registerDao.Username, registerDao.Password, registerDao.Email)) < 1)
            {
                return true;
            }
            return false;
        }//注册

        public int getBlance(string user_id)
        {
            string sql = "select Blog_user_balance from blog_user where Blog_user_id='{0}'";
            return Conn.DataValue(string.Format(sql, user_id));
        }

        public User_msgDao User_msg(string user_id) {
            string sql = "select " +
                "Blog_user_portrait," +
                "Blog_user_level," +
                "(select count(Blog_article_id) from blog_article where Blog_user_id = users.Blog_user_id) as article_count," +
                "(select count(Blog_fans_follow) from blog_fans where Blog_user_id = users.Blog_user_id) as follow_count," +
                "(select count(Blog_user_id) from blog_fans where Blog_fans_follow = users.Blog_user_id) as fans_count," +
                "(select count(Blog_comment_id) from blog_comment where Blog_user_id = users.Blog_user_id and Blog_comment_state = 1) as comment_count," +
                "Blog_user_balance," +
                "(select SUM(Blog_order_price) from blog_order where Blog_user_id = users.Blog_user_id and Blog_order_state = 1) as shopCount," +
                "(select Blog_grade_name from blog_grade where Blog_grade_id=users.Blog_user_Grade) as grade " +
                "from blog_user as users " +
                "where Blog_user_id = '{0}'";
            object[] arr = Conn.DataRowOne(string.Format(sql, user_id));
            return new User_msgDao(arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[3].ToString(), arr[4].ToString(), arr[5].ToString(), arr[6].ToString(), arr[7].ToString(), arr[8].ToString());
        }//获取用户数据到用户中心

        public bool isExist(string username) {
            string sql = "select count(Blog_user_id) from blog_user where Blog_user_name='{0}'";
            if (Conn.DataValue(string.Format(sql, username)) > 0) {
                return true;
            }
            return false;
        }//判断指定用户名是否存在

        public bool UpPwd(string user_id, string newPwd) {
            string sql = "update blog_user set Blog_user_Password='{0}' where Blog_user_id='{1}'";
            if (Conn.AddDelUp(string.Format(sql, newPwd, user_id)) < 1) {
                return true;
            }
            return false;
        }//修改密码

        public bool upemail(string user_id, string newEmail) {
            string sql = "update blog_user set Blog_user_email='{0}' where Blog_user_id='{1}'";
            if (Conn.AddDelUp(string.Format(sql, newEmail, user_id)) < 1) {
                return true;
            }
            return false;
        }//修改邮箱

        public List<User_msgDao> UserAllMsg(string user_name, string grade, int page) {
            List<User_msgDao> list = new List<User_msgDao>();
            string sql = "select " +
                "Blog_user_portrait," +
                "Blog_user_level,(" +
                "select count(Blog_article_id) from blog_article where Blog_user_id = users.Blog_user_id and Blog_article_state = 1) as articleCount," +
                "(select count(Blog_fans_follow) from blog_fans where Blog_user_id = users.Blog_user_id) as FollowCount," +
                "(select count(Blog_user_id) from blog_fans where Blog_fans_follow = users.Blog_user_id) as fansCOunt," +
                "(select count(Blog_comment_id) from blog_comment where Blog_user_id = users.Blog_user_id and Blog_comment_state = 1) as CommentCount," +
                "Blog_user_balance," +
                "(select IFNULL(sum(Blog_order_price),0) from blog_order where Blog_user_id = users.Blog_user_id) as ShopCount," +
                "Blog_user_Grade," +
                "Blog_user_id," +
                "Blog_user_name ," +
                "ifnull(Blog_user_email,'') " +
                "from blog_user as users " +
                "where Blog_user_name like '%{0}%' and Blog_user_Grade like '{1}' and Blog_user_state =1 " +
                "GROUP BY Blog_user_id " +
                "ORDER BY Blog_user_id desc " +
                "limit {2},20";
            MySqlDataReader dr = Conn.DataListAll(string.Format(sql, user_name, grade, page));
            while (dr.Read()) {
                User_msgDao user_MsgDao = new User_msgDao(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetString(9), dr.GetString(10), dr.GetString(11));
                list.Add(user_MsgDao);
            }
            return list;

        }//所有用户的数据
        public int UserAllMsgCount(string user_name, string grade) {
            string sql = "select count(Blog_user_id) from blog_user where Blog_user_name like '%{0}%' and Blog_user_Grade like '{1}' and Blog_user_state='1'";
            return Conn.DataValue(string.Format(sql, user_name, grade));
        }//获取所有用户的总数

        public bool examine(string user_id) {
            string sql = "delete from blog_user where  Blog_user_id='{0}'";
            if (Conn.AddDelUp(string.Format(sql, user_id)) < 1) {
                return true;
            }
            return false;
        }//删除用户

        public bool UpGrade(string user_id, string grade) {
            string sql = "update blog_user set Blog_user_Grade='{0}' where Blog_user_id='{1}'";
            if (Conn.AddDelUp(string.Format(sql, grade, user_id)) < 1) {
                return true;
            }
            return false;
        }//修改权限

        public List<User_msgDao> EditUserMsg(string user_id) {
            List<User_msgDao> list = new List<User_msgDao>();
            string sql = "select Blog_user_name,ifnull(Blog_user_email,''),Blog_user_balance,Blog_user_Grade from blog_user where Blog_user_id='{0}'";
            object[] arr = Conn.DataRowOne(string.Format(sql, user_id));
            User_msgDao user_MsgDao = new User_msgDao(arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[3].ToString());
            list.Add(user_MsgDao);
            return list;

        }//编辑信息

        public bool UpUser(string password, string email, string balance, string grade, string user_id) {
            string sql = "update blog_user set Blog_user_Password='{0}',Blog_user_email='{1}',Blog_user_balance='{2}',Blog_user_Grade='{3}' where Blog_user_id='{4}'";
            if (Conn.AddDelUp(string.Format(sql, password, email, balance, grade, user_id)) < 1) {
                return true;
            }
            return false;
        }//后台管理员更新用户

        public bool UpUserPortrait(string path, string user_id)
        {
            string sql = "update blog_user set Blog_user_portrait='{0}' where Blog_user_id='{1}'";
            if (Conn.AddDelUp(string.Format(sql, path, user_id)) < 1) {
                return true;
            }
            return false;
        }
        public bool ResetPassword(string user_name,string user_email){
            string sql = "update blog_user set Blog_user_Password='{0}' where Blog_user_name='{1}' and Blog_user_email='{2}'";
            if (Conn.AddDelUp(string.Format(sql, "A123456", user_name,user_email)) < 1)
            {
                return true;
            }
            return false;
        }//根据用户名，邮箱修改密码

        public bool User_nameAndEmail(string user_name,string email) {
            string sql = "select count(Blog_user_id) from blog_user where Blog_user_name='{0}' and Blog_user_email='{1}'";
            if (Conn.DataValue(string.Format(sql,user_name,email))<1) {
                return true;
            }
            return false;
        }//根据用户名邮箱查询是有用户
    }
}